﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using XT.Exam.BLL;
using XT.Exam.Common;
using System.Data.SqlClient;
using XT.Exam.MODEL;
using XT.Exam.IBLL;
using XT.Exam.IDataAccess;
using Newtonsoft.Json;
using System.Data.OleDb;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
namespace ExamSystemMvc.Controllers
{
    public class SysUserInfoController : Controller
    {
        //
        // GET: /SysUserInfo/

        public ActionResult Index()
        {
            return View();
        }
        /// <summary>
        /// 欢迎页面
        /// </summary>
        /// <returns></returns>
        public ActionResult Welcome()
        {
            return View();
        }
        /// <summary>
        /// 学生列表管理
        /// </summary>
        /// <returns></returns>
        public ActionResult StudentShow()
        {
            SysStudentBLL student = new SysStudentBLL();
            List<Student> list = student.GetList<Student>();
            return View(list);
        }
        /// <summary>
        /// 开始组卷
        /// </summary>
        /// <returns></returns>
        public ActionResult TestPaper()
        {
            return View();
        }
        /// <summary>
        /// 学院
        /// </summary>
        /// <returns></returns>
        public string CollegeList()
        {
            SysCollageBLL collage = new SysCollageBLL();
            List<SystemCollege> list = collage.GetList<SystemCollege>();
            var reault = JsonConvert.SerializeObject(list);
            return reault;
        }
        /// <summary>
        /// 系
        /// </summary>
        /// <returns></returns>
        public string TieList(int ParentID)
        {
            SysTieBLL tie = new SysTieBLL();
            SqlParameter[] parameter = { new SqlParameter("@ParentID", ParentID) };
            List<Tie> list = tie.GetList<Tie>(parameter);
            var reault = JsonConvert.SerializeObject(list);
            return reault;
        }
        /// <summary>
        /// 阶段
        /// </summary>
        /// <param name="ParentID"></param>
        /// <returns></returns>
        public string PhaseTableList(int ParentID)
        {
            SysPhaseTableBLL phasetable = new SysPhaseTableBLL();
            SqlParameter[] parameter = { new SqlParameter("@ParentID", ParentID) };
            List<PhaseTable> list = phasetable.GetList<PhaseTable>(parameter);
            var reault = JsonConvert.SerializeObject(list);
            return reault;
        }
        /// <summary>
        /// 添加学生
        /// </summary>
        /// <param name="stu"></param>
        /// <returns></returns>
        public ActionResult AddStudent(Student stu)
        {
            stu.Student_Pass = MD5DBhelper.MD5Encrypt32("000000");
            SysStudentBLL student = new SysStudentBLL();
            SqlParameter[] parameter =
            {
                new SqlParameter("@Student_Numb",stu.Student_Numb),
                new SqlParameter("@Student_Name",stu.Student_Name),
                new SqlParameter("@Student_Sex",stu.Student_Sex),
                new SqlParameter("@Student_Email",stu.Student_Email),
                new SqlParameter("@Student_Tel",stu.Student_Tel),
                new SqlParameter("@Student_Card",stu.Student_Card),
                new SqlParameter("@RoleID",1),
            };
            bool reault = student.Add(stu, parameter);
            return View();
        }
        /// <summary>
        /// 导入
        /// </summary>
        /// <returns></returns>
        public ActionResult leadingin()        
        {
            return View();
        }
        /// <summary>
        /// 导入预览
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        //HttpPostedFileBase：充当类的基类，这些类提供对客户端已上载的单独文件的访问
        public ActionResult Browse(HttpPostedFileBase file)
        {
            if (string.Empty.Equals(file.FileName) || ".xls" != Path.GetExtension(file.FileName))
            {
                throw new ArgumentException("当前文件格式不正确,请确保正确的Excel文件格式!");
            }
            var severPath = this.Server.MapPath("/files/"); //获取当前虚拟文件路径
            var savePath = Path.Combine(severPath, file.FileName); //拼接保存文件路径
            try
            {
                file.SaveAs(savePath);
                DataTable dt = this.ImportExcel(savePath);
                List<Student> list = new List<Student>();
                foreach (DataRow item in dt.Rows)
                {
                    list.Add(new Student()
                    {
                        Student_Numb = item["学生学号"].ToString(),
                        Student_Pass = item["密码"].ToString(),
                        Student_Name = item["姓名"].ToString(),
                        Student_Email = item["邮箱"].ToString(),
                        Student_Tel = item["手机号"].ToString(),
                        Student_Card = item["身份证号"].ToString(),
                        Student_Sex = item["性别"].ToString(),
                        Lecturer_Name = item["讲师"].ToString(),
                        Class_Name = item["班级"].ToString(),
                    });
                }
                ViewBag.list = list;
                return View("leadingin");
            }
            finally
            {
                System.IO.File.Delete(savePath);//每次上传完毕删除文件
            }
        }

        /// <summary>
        /// 导入学生信息
        /// </summary>
        /// <returns></returns>
        public int AddAll()
        {
            List<Student> list = (List<Student>)Session["list"];
            string connstring = "Data Source=.;Initial Catalog=考试系统;Persist Security Info=True;User ID=sa;Password=sa";
            using (SqlConnection sql = new SqlConnection(connstring))
            {
                //打开数据库
                sql.Open();
                //连接数据库字符串
                string str = "";
                foreach (var item in list)
                {
                    str += @"INSERT INTO [考试系统].[dbo].[Student]([Student_Numb],[Student_Name]
,[Student_Pass],[Student_Email],[Student_Tel],[Student_Card],[Student_Sex],[Lecturer_Name],[Class_Name],[RoleID])VALUES('" + item.Student_Numb + "','" + item.Student_Name + "','" + MD5DBhelper.MD5Encrypt32(item.Student_Pass) + "','" + item.Student_Email + "','" + item.Student_Tel + "','" + item.Student_Card + "','" + item.Student_Sex + "','" + item.Lecturer_Name + "','" + item.Class_Name + "',1)" + ";";
                }
                //截取最后一个字符
                str = str.Substring(0, str.Length - 1);
                SqlCommand cmd = new SqlCommand(str, sql);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return cmd.ExecuteNonQuery();
                }
                else
                    return 0;
            }
        }
        public ActionResult ExamProd()
        {
            return View();
        }























        /// <summary>
        /// 导入辅助
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellValue(NPOI.SS.UserModel.ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator hfs = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        hfs.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch (Exception)
                    {

                        return cell.NumericCellValue.ToString();
                    }
            }
        }
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataTable ImportExcel(string filePath)
        {
            HSSFWorkbook hssfworkbook;
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception err)
            {

                throw err;
            }
            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
            DataTable dt = new DataTable();
            IRow headerrow = sheet.GetRow(0);  //获取标题行数据
            int cell = headerrow.LastCellNum;
            int row = sheet.LastRowNum;
            for (int i = headerrow.FirstCellNum; i < cell; i++)
            {
                DataColumn column = new DataColumn(headerrow.GetCell(i).StringCellValue);
                dt.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i <= row; i++)
            {
                IRow rows = sheet.GetRow(i);
                DataRow datarow = dt.NewRow();
                if (rows != null)
                {
                    for (int j = rows.FirstCellNum; j < cell; j++)
                    {
                        if (rows.GetCell(j) != null)
                        {
                            datarow[j] = GetCellValue(rows.GetCell(j));
                        }
                    }
                }
                dt.Rows.Add(datarow);
            }
            return dt;
        }
    }
}
